RE: [SQL] Problems with default date 'now'

At 18:14 +0300 on 10/6/98, Jackson, DeJuan wrote:


> >
> Why don't you just use the function version of now (I'm not familiar
> with 6.2.1 so it could be that it doesn't exist).
>
> received_date DATE DEFAULT NOW()

As I understand it, date is not the same as datetime, and datetime is the
most precise and wide-ranging of PostgreSQL various date and time types
(too many by half, if you ask me). It also has most of the functions.

Well, you say, why not just convert now() into datetime (because it's of
type date, and undocumented in the pgbuiltin page, at least in version
6.2.1).

Well, the reason lies here:

testing=> select datetime(now()), 'now'::datetime;
datetime                    |?column?
----------------------------+----------------------------
Wed Jun 10 22:03:54 1998 IDT|Wed Jun 10 19:03:54 1998 IDT
(1 row)

The time was 19:03:54. Why three hours? Because IDT is GMT+3. This is an
old problem, in which certain timezones lose their differential from GMT in
the conversion between date and datetime. Ahem.

Another solution suggested in the past was datetime( 'now'::text ), since
the datetime conversion is a function. Well, this is what it does:

testing=> CREATE TABLE tst1 (
testing->     stamp datetime DEFAULT datetime( 'now'::text ) NOT NULL
testing-> );
PQexec() -- Request was sent to backend, but backend closed the channel
before responding.  This probably means the backend terminated abnormally
before or while processing the request.

Ahem, ahem. The reason for this one is that the backend cannot handle the
explicit type cast (::text) in an argument to a function.

In short, I found a way, it works around all the known bugs in 6.2.1, so
that's the way I use until I can upgrade to 6.3.2.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma